package com.example.dao;

import com.example.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * 学生基本信息访问类
 * @author 高合
 * @date 2022-6-7
 */

@Component
@Slf4j
public class StuInforDao {
    @Autowired
    @Qualifier("apiJdbcTemplate")
    protected JdbcTemplate jdbcTemplate;

    private String getWhereSQL(PageInfo pageInfo){
        String search= pageInfo.getSearch();
        if(search!=null&&search!=""){
            return String.format("where phone like '%s%s%s' ","%",search,"%");
        }else {
            return "";
        }
    }

    /**
     * 查询学生信息表的总行数
     * @param pageInfo
     * @return
     */
    public int queryAllStuInforCount(PageInfo pageInfo){
        String SQL= "select count(*) as CN from student_information";
        SQL+=getWhereSQL(pageInfo);
        log.info(SQL);
        List<Map<String,Object>> rs=jdbcTemplate.queryForList(SQL);
        if (rs!=null && rs.size()>0){
            return Integer.parseInt(rs.get(0).get("CN").toString());//将字符串转换十进制整数
        }
        return 0;
    }

    /**
     * 查询当前页的学生基本信息记录
     * @param pageInfo
     * @return
     */
    public List<StudentInformation> queryAllStuInforPage(PageInfo pageInfo){
        String SQL = "select * from student_information";
        SQL += getWhereSQL(pageInfo);
        SQL +=String.format(" limit %d,%d",
                pageInfo.getPage_size()*(pageInfo.getPage_index()-1),
                pageInfo.getPage_size());
        log.info(SQL);
        RowMapper rowMapper = new BeanPropertyRowMapper<StudentInformation>(StudentInformation.class);
        return jdbcTemplate.query(SQL,rowMapper);
    }

    /**
     * 填写学生基本信息
     *
     * @param studentInformation
     */
    public void addStudInfor(StudentInformation studentInformation){
        try {
            String sql = String.format("insert into student_information(id,student_id,name,phone,identity_id,college,classroom,green_code) values('%s','%s','%s','%s','%s','%s','%s','%s')",
                    studentInformation.getId(), studentInformation.getStudentId(), studentInformation.getName(), studentInformation.getPhone(), studentInformation.getIdentityId(), studentInformation.getCollege(), studentInformation.getClassroom(),studentInformation.getGreenCode());
            String sql2 = String.format("insert into result(id,studentId,first_trial_result,vote_result,intermediate_result,final_result) values('%s','%s','%s','%s','%s','%s')",
                    studentInformation.getId(),studentInformation.getStudentId(),"2","2","2","2");
            log.info(sql);
            log.info(sql2);
            jdbcTemplate.execute(sql2);
            jdbcTemplate.execute(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 修改学生基本信息
     * @param studentInformation
     * @return
     */
    public boolean editStuInfor(StudentInformation studentInformation){
        try{
            String SQL = String.format("update student_information set id" +
                            "studentId='%s',name='%s',phone='%s',identity_id='%s',college='%s',classroom='%s',green_code='%s'",
                    studentInformation.getStudentId(),studentInformation.getName(),studentInformation.getPhone(),studentInformation.getIdentityId(),studentInformation.getClass(),
                    studentInformation.getCollege(),studentInformation.getGreenCode());
            log.info(SQL);
            jdbcTemplate.execute(SQL);
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除学生基本信息记录
     * @param stuifor_id
     * @return
     */
    public boolean deleteStuInfor(String stuifor_id){
        String SQL=String.format("delete from student_information where id='%s'",stuifor_id);
        jdbcTemplate.execute(SQL);
        return true;
    }

    /**
     * 查找一个学生基本信息
     * @param
     * @return
     */
    public StudentInformation queryOneStuInfor(String studentId){
        String SQL =String.format("select * from student_information where student_id='%s'",studentId);
        log.info(SQL);
        RowMapper<StudentInformation> rowMapper=new BeanPropertyRowMapper<StudentInformation>(StudentInformation.class);
        List<StudentInformation> rs=jdbcTemplate.query(SQL,rowMapper);
        if(rs!=null&&rs.size()>0){
            return rs.get(0);
        }else {
            return null;
        }
    }

    /**
     * 保存文件属性
     * @param studentFile
     * @return
     */
    public void saveFile(StudentFile studentFile){
        try {
            String SQL = String.format("insert into file(date_dir_path,stu_id,file_id,file_name,size,file_oldname,type,time) values('%s','%s','%s','%s','%s','%s','%s','%s')",
                    studentFile.getDateDirPath(), studentFile.getStu_id(), studentFile.getFile_id(), studentFile.getFile_name(), studentFile.getSize(), studentFile.getFile_oldname(), studentFile.getType(),studentFile.getTime());
            log.info(SQL);
            jdbcTemplate.execute(SQL);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public Result queryResult(String id) {
        String SQL = String.format("select * from result where id='%s'",id);
        log.info(SQL);
        RowMapper<Result> rowMapper = new BeanPropertyRowMapper<>(Result.class);
        List<Result> list = jdbcTemplate.query(SQL,rowMapper);
        if (list!=null && list.size()>0){
            return list.get(0);
        }
        return null;
    }
    public StudentFile queryOneStudentFile(String sutdentId){
        String SQL = String.format("select * from file where student_id = '%s' ",sutdentId);
        log.info(SQL);
        RowMapper<StudentFile> rowMapper = new BeanPropertyRowMapper<>(StudentFile.class);
        List<StudentFile> list = jdbcTemplate.query(SQL,rowMapper);
        if (list != null && list.size() > 0){
            return list.get(0);
        }
        return null;
    }

}
